using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using QLNhaSach.BUS.DTO;
namespace QLNhaSach.DAO
{
    public class SachDAO
    {
        //Load danh sach sach
        public static DataTable DSSach()
        {
            string sql = "SELECT* FROM Sach s,TacGia tg,TheLoai tl,NhaXuatBan nxb WHERE s.MaTL = tl.MaTL and ";
            sql += "s.MaTG = tg.MaTG and nxb.MaNXB = s.MaNXB";
            return Config.AccessHelper.ExecuteQuery(sql);

        }
        
        //Them mot loai sach moi
        public static bool ThemSach(SachDTO b)
        {
            try
            {
                string sql = "INSERT INTO Sach(TenSach,Gia,MaTL,MaTG,MaNXB,MoTa,SLNhap)VALUES('" +
                b.TenSach + "'," +
                b.Gia + "," +
                b.MaTL + "," +
                b.MaTG + "," +
                b.MaNXB + ",'" +
                b.MoTa + "'," +
                b.SoLuongNhap + ")";
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }
        
        //Cap nhat thong tin mot loai sach
        public static bool CapNhatThongTinSach(SachDTO b)
        {
            try
            {
                string sql = "UPDATE Sach SET TenSach = '" + b.TenSach + "', Gia = " + b.Gia;
                sql += ", MaTL = " + b.MaTL + ", MaTG = " + b.MaTG + ", MaNXB = " + b.MaNXB;
                sql += ", MoTa = '" + b.MoTa + "' WHERE (MaSach = " + b.MaSach + ")";
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }

        //Xoa sach khoi danh sach
        public static bool XoaSach(SachDTO b)
        {
            try
            {
                string sql = "DELETE FROM Sach where MaSach=" + b.MaSach;
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }

        //Tim kiem sach trong he thong(so trung du lieu)
        public static DataTable TimKiemSach(SachDTO b)
        {
            string sql = "SELECT* FROM Sach where TenSach LIKE%'" + b.TenSach + "'OR MaTL LIKE%'"+
                                b.MaTL +"'OR MaTG LIKE%'"+b.MaTG +"'OR MaNXB LIKE%'"+b.MaNXB+"' OR Gia='"+b.Gia+"'";
             return Config.AccessHelper.ExecuteQuery(sql);
        }

        //Tim kiem sach dua vao nhieu bang
        public static DataTable TimKiemSachNhieuBang(string sql)
        {
            return Config.AccessHelper.ExecuteQuery(sql);
        }

        //Lay mot cuon sach dua vao ma
        public static DataTable LaySach(int maSach)
        {
            string sql = "SELECT * FROM Sach WHERE (MaSach = " + maSach + ")";
            return Config.AccessHelper.ExecuteQuery(sql);
        }

        public static DataTable KiemTraTenSach(string tenSach)
        {
            string sql = "SELECT * FROM Sach WHERE (TenSach = '" + tenSach + "')";
            return Config.AccessHelper.ExecuteQuery(sql);
        }
    }
}
